﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InstrumentCheck.Module;
using System.Data;
using System.Data.SqlClient;

namespace InstrumentCheck.DAL
{
    public class User_dal
    {
        public User_dal()
        { }

        public User GetUserInfo_dal(string strEmpNO)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            string sql = "SELECT dbo.f_GetEmpBossNO(@EmpNO) BossNO,resak015 DeptNO,resak005 Email,";
            sql += " resak002 EmpChineseName,resak004 EmpEngName,resak001 EmpNO,resak036 ExtNO,";
            sql += " dbo.f_IsToolingRoom(@EmpNO) IsToolingRoom FROM resak WHERE resak001 = @EmpNO";
            SqlParameter[] para = new SqlParameter[1];
            para[0] = SQLHelper.SQLHelper.CreateParameter("EmpNO", SqlDbType.VarChar, strEmpNO, ParameterDirection.Input);
            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, para);
            if (ds.Tables[0].Rows.Count > 0)
                return GenerateUserBySource(ds.Tables[0].Rows[0]);
            else
                return null;
        }

        public User GetUserInfoByName_dal(string strUserName)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlParameter[] para = new SqlParameter[1];
            para[0] = SQLHelper.SQLHelper.CreateParameter("strName", SqlDbType.VarChar, strUserName, 100, ParameterDirection.Input);
            SQLHelper.SQLHelper.Fill(da, ds, "sp_GetUserInfoByName", CommandType.StoredProcedure, para);

            if (ds.Tables[0].Rows.Count > 0)
                return GenerateUserBySource(ds.Tables[0].Rows[0]);
            else
                return null;
        }

        private User GenerateUserBySource(DataRow row)
        {
            User user = new User();
            user.BossNO = row["BossNO"].ToString();
            user.DeptNO = row["DeptNO"].ToString();
            user.Email = row["Email"].ToString();
            user.EmpChineseName = row["EmpChineseName"].ToString();
            user.EmpEngName = row["EmpEngName"].ToString();
            user.EmpNO = row["EmpNO"].ToString();
            user.ExtNO = row["ExtNO"].ToString();
            user.IsToolingRoom = Convert.ToBoolean(row["IsToolingRoom"]);
            return user;
        }

        public List<User> GetQueryUser_dal(string strColumnName, string strQueryValue,bool IsInCorrectItem)
        {
            List<User> list = new List<User>();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            string sql = "";
            if (!IsInCorrectItem)
            {
                sql = "SELECT top 500 dbo.f_GetEmpBossNO(resak001) BossNO,resak015 DeptNO,resak005 Email,";
                sql += " resak002 EmpChineseName,resak004 EmpEngName,resak001 EmpNO,resak036 ExtNO,";
                sql += " dbo.f_IsToolingRoom(resak001) IsToolingRoom FROM resak WHERE " + strColumnName + " like @strQueryValue"; 
            }

            if (IsInCorrectItem)
            {
                sql = "SELECT top 500 dbo.f_GetEmpBossNO(resak001) BossNO,resak015 DeptNO,resak005 Email,";
                sql += " resak002 EmpChineseName,resak004 EmpEngName,resak001 EmpNO,resak036 ExtNO,";
                sql += " dbo.f_IsToolingRoom(resak001) IsToolingRoom FROM resak WHERE " + strColumnName + " like @strQueryValue";
                sql += " and (resak001 in (select user_id  from tbl_master a ,correct b  where a.ast_no = b.fas_NO) ";
                sql += " or resak001 in (select user_id  from tbl_control a ,correct b  where a.ast_no = b.fas_NO)) ";
            }

            SqlParameter[] para = new SqlParameter[1];
            para[0] = SQLHelper.SQLHelper.CreateParameter("strQueryValue", SqlDbType.VarChar, strQueryValue, ParameterDirection.Input);
            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, para);

            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach(DataRow row in ds.Tables[0].Rows)
                {
                    list.Add(GenerateUserBySource(row));
                }
                return list;
            }
            else
                return null;
        }

    }
}
